﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

namespace SDATA
{
    public class DATA
    {
        public static SqlConnection Connection(string connectString, bool noti=true)
        {
            SqlConnection cnn = new SqlConnection(connectString);
            try
            {
                cnn.Open();
            }
            catch (Exception ex)
            {
                if (noti) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
            }
            return cnn;
        }
        public static SqlDataReader DataReader(string sql, string connectString)
        {
            SqlConnection cnn = Connection(connectString);
            SqlDataReader dr = null;            
            SqlCommand cmd = new SqlCommand(sql, cnn);            
            try
            {
                dr = cmd.ExecuteReader();
            }
            catch
            {
                
            }
            return dr;
        }
        public static List<string> GetListSimple(string sql, string connectString)
        {
            List<string> ListSimple = new List<string>();
            SqlDataReader dr = DataReader(sql, connectString);
            if (dr == null) return ListSimple;            
            while (dr.Read())
            {
                var myString = dr.GetString(0);
                ListSimple.Add(myString);
            }
            return ListSimple;
        }

        public static bool CheckData(string connectString)
        {
            SqlConnection cnn = Connection(connectString);
            if (cnn.State == System.Data.ConnectionState.Open)
            {
                List<string> ListTable = DATA.GetListSimple("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", connectString);
                if(ListTable.Count>0)
                    return true;
            }
                
            return false;
        }

        public static DataTable GetDataTable(string sql, string connectString, bool noti = true)
        {
            DataTable  dt = new DataTable();         
            SqlConnection cn = Connection(connectString,noti);
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            try
            {
                da.Fill(dt);
                cn.Close();
            }
            catch
            {
                return null;
            }
            return dt;
        }

        public static bool ExecuteNonQuery(string sql, string connectString, bool noti = true)
        {
            SqlConnection cnn = Connection(connectString);
            SqlCommand cmd = new SqlCommand(sql, cnn);
            try
            {
                cmd.ExecuteNonQuery();
                cnn.Close();
                return true;
            }
            catch(Exception ex)
            {
                if (noti) { MessageBox.Show(ex.Message); }
                cnn.Close();
                return false;
            }
        }

        public static bool ExecuteFAST(string sql, string connectString, string timeout = "30", bool noti = true)
        {
            SqlConnection cnn = new SqlConnection(connectString+";Connection Timeout="+timeout);
            cnn.Open();
            SqlCommand cmd = new SqlCommand(sql, cnn);
            try
            {
                cmd.ExecuteNonQuery();
                cnn.Close();
                return true;
            }
            catch (Exception ex)
            {
                if (noti) { MessageBox.Show(ex.Message); }
                cnn.Close();
                return false;
            }
        }

    }
}
